package com.newcapec.dao.impl;

import com.newcapec.bean.UserEntity;
import com.newcapec.dao.SysUserDao;
import com.newcapec.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 *
 */
public class SysUserDaoImpl implements SysUserDao {
    private Connection conn;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;
    @Override
    public boolean addUser(UserEntity userEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "insert into sys_user (name,acct,pwd,crtime,uptime)" +
                    "values (?,?,?,now(),now())";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,userEntity.getName());
            pstmt.setString(2,userEntity.getAcct());
            pstmt.setString(3,userEntity.getPwd());
            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//添加成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public boolean deleteUser(String id) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "delete from sys_user where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setInt(1,Integer.parseInt(id));

            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//删除成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }



    @Override
    public boolean updateUser(UserEntity userEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "update sys_user set name = ?,acct = ?,pwd = ?,uptime = now() where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,userEntity.getName());
            pstmt.setString(2,userEntity.getAcct());
            pstmt.setString(3,userEntity.getPwd());
            pstmt.setInt(4,userEntity.getId());
            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//修改成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    /**
     * 查询所有用户的方法
     * @return List集合
     */
    @Override
    public List<UserEntity> queryAll(int offset,int pageSize,UserEntity user) {
        List<UserEntity> userList = new ArrayList<>();
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user ";
            if (user != null && user.getName() != null){
                sql += "where name like ?";
            }
            sql += "limit ?,?";

            pstmt = conn.prepareStatement(sql);
            if (user != null && user.getName() != null){
                pstmt.setString(1,"%" + user.getName() + "%");
                pstmt.setInt(2,offset);
                pstmt.setInt(3,pageSize);
            }else {
                pstmt.setInt(1,offset);
                pstmt.setInt(2,pageSize);
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                UserEntity userEntity = new UserEntity();
                userEntity.setId(rs.getInt("id"));
                userEntity.setName(rs.getString("name"));
                userEntity.setAcct(rs.getString("acct"));
                userEntity.setPwd(rs.getString("pwd"));
                userEntity.setCrtime(rs.getString("crtime"));
                userEntity.setUptime(rs.getString("uptime"));
                userList.add(userEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return userList;
    }

    @Override
    public UserEntity queryUserById(String id) {
        UserEntity userEntity = null;//没查到返回一个null
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(id));
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()){
                userEntity = new UserEntity();
                userEntity.setId(rs.getInt("id"));
                userEntity.setName(rs.getString("name"));
                userEntity.setAcct(rs.getString("acct"));
                userEntity.setPwd(rs.getString("pwd"));
                userEntity.setCrtime(rs.getString("crtime"));
                userEntity.setUptime(rs.getString("uptime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return userEntity;
    }

    @Override
    public UserEntity queryUserByAcct(String acct) {
        UserEntity userEntity = null;//没查到返回一个null
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,name,acct,pwd,crtime,uptime from sys_user where acct = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,acct);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()){
                userEntity = new UserEntity();
                userEntity.setId(rs.getInt("id"));
                userEntity.setName(rs.getString("name"));
                userEntity.setAcct(rs.getString("acct"));
                userEntity.setPwd(rs.getString("pwd"));
                userEntity.setCrtime(rs.getString("crtime"));
                userEntity.setUptime(rs.getString("uptime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return userEntity;
    }

    @Override
    public int getAllCount(String name) {
        conn = JDBCUtil.getConn();
        try {
            String sql = "select count(1) from sys_user ";
            if (name != null){
                sql += "where name like ?";
            }


            pstmt = conn.prepareStatement(sql);
            if (name != null){
                pstmt.setString(1,"%" + name + "%");
            }
            rs = pstmt.executeQuery();
            if (rs.next()){
                return rs.getInt(1);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return 0;
    }




}
